from flask import Blueprint
from flask import request,jsonify
import sqlite3
import os
import fitz

competition_blue = Blueprint('competition', __name__)

def pdf2image(pdfPath, imgPath)->int:
    """返回pdf页数"""
    # 打开PDF文件
    pdf = fitz.open(pdfPath)
    plen = pdf.page_count
    # 逐页读取PDF
    for pg in range(0, len(pdf)):
        page = pdf[pg]
        # 获取每页的图片对象
        pm = page.get_pixmap(matrix=fitz.Matrix(3, 3), colorspace='rgb')
        # 开始写图像
        pm.save(imgPath+'/' + f'{pg:04d}' + ".jpg",output='JPEG',jpg_quality=98)
    pdf.close()
    return plen


def read_table():
    """返回msg表的所有数据"""
    """返回：[(id,content,time,type),(id,content,time,type),...]"""
    # 读取数据库表
    try:
        conn = sqlite3.connect('contest.db')
        c = conn.cursor()
        sql = '''SELECT * FROM msg'''
        c.execute(sql) # 执行sql语句
        result = c.fetchall() # 获取结果
        conn.close() # 关闭数据库连接
        return result
    except:
        return '数据库出错！'


def generate_id(_id:int):
    """msg表，生成id"""
    """参数：id的前6位，日期"""
    """返回：6位id"""
    
    # 查询数据库中已有的数目
    conn = sqlite3.connect('contest.db')
    c = conn.cursor()
    sql = '''SELECT id FROM msg WHERE id LIKE ? order by id desc''' # 获取当前日期相关的id，并降序排列
    c.execute(sql,(_id+'%',))
    result = c.fetchall()
    print("查询结果：",result)
    conn.close()

    # 取第一个结果的后两位（确保删除操作不会影响到id的生成）
    if len(result)==0:
        num = 1
    else:
        num = int(str(result[0][0])[-2:])+1

    id =  f'{_id}{num:02}'
    print("生成id：",id)
    return id


@competition_blue.route('/deleteCompetitions', methods=['GET'])
def delete_competition():
    id = request.args['id']

    sql = '''DELETE FROM msg WHERE id=?'''
    conn = sqlite3.connect('contest.db')
    c = conn.cursor()
    c.execute(sql,(id,))
    conn.commit()
    conn.close()

    return jsonify({'msg': '删除成功','code':200,'data':{}})
    
@competition_blue.route('/addCompetitions', methods=['POST'])
def add_competition():
    try:
        folder_path = '/home/files/xhq' # 主文件夹路径
        # folder_path = './msg' # 主文件夹路径
        # 获取表单数据
        content = request.form['content']
        time = request.form['time']
        type = request.form['type']
        pdf = request.files['pdf']
        _id = time.split('-')[0][-2:]+time.split('-')[1]+time.split('-')[2] # id前6位
        
        print("获取数据：",content,time,type,pdf,_id)
        id = generate_id(_id)
        
        # 保存文件
        file_path = folder_path+'/'+id # 子文件夹路径
        os.path.exists(file_path) or os.mkdir(file_path) # 创建子文件夹
        pdf.save(file_path+'/'+id+'.pdf')

        # pdf转图片
        pdf_path = file_path+'/'+id+'.pdf'
        pages = pdf2image(pdf_path, file_path)

        # 数据持久化
        conn = sqlite3.connect('contest.db')
        c = conn.cursor()
        sql = '''INSERT INTO msg (id,content,time,type,pages) VALUES (?,?,?,?,?)'''
        c.execute(sql,(id,content,time,type,pages))
        conn.commit()
        conn.close()
        return jsonify({'msg': '添加成功','code':200,'data':{}})
    except Exception as e:
        print(e)
        return jsonify({'msg': '添加失败','code':200,'data':e})

@competition_blue.route('/updateCompetitions', methods=['POST'])
def update_competition():
    
    # 获取表单数据
    content = request.form['content']
    time = request.form['time']
    type = request.form['type']
    id = request.form['id']

    # 修改数据
    try:
        conn = sqlite3.connect('contest.db')
        c = conn.cursor()
        sql = '''UPDATE msg SET content=?,time=?,type=? WHERE id=?'''
        c.execute(sql,(content,time,type,id))
        conn.commit()
        conn.close()
        return jsonify({'msg': '修改成功','code':200,'data':{}})
    except:
        return jsonify({'msg': '修改失败','code':200,'data':{}})
   
@competition_blue.route('/getCompetitions', methods=['GET'])
def get_competition():
    res = read_table()
    if res=='数据库出错！':
        return jsonify({'msg': '获取失败，数据库出错','code':500,'data':{}})
    else:
        data = []
        for i in res:
            id,content,time,type,pages = i
            data.append({'id':id,'content':content,'time':time,'type':type,'pages':pages})
        return jsonify({'msg': '获取成功','code':200,'data':data})
